AltSymbolMap

V8 Message Definiton

METADATA

Attribute	Value
Topic	1800-client-config
MLink Token	ClientControl
Product	SRControl
accessType	SELECT,UPDATE,INSERT,DELETE

Table Definition

Field	Type	Key	Default Value	Comment
ticker_at	enum - AssetType	PRI	'None'
ticker_ts	enum - TickerSrc	PRI	'None'
ticker_tk	VARCHAR(12)	PRI	''
srcAccnt	VARCHAR(16)	PRI	''	client enter order in this account
clientFirm	VARCHAR(16)	PRI	''	SR assigned client firm acronym
accnt	VARCHAR(16)		''	this accnt will replace accnt on parent order if exists
altAccnt	VARCHAR(16)		''	will replace altAccnt on parent order if exists
altUserName	VARCHAR(24)		''	will replace altUserName on parent order if exists
strategy	VARCHAR(36)		''	will replace strategy on parent order if exists
userData1	TINYTEXT		''	will replace userData1 on parent order if exists
userData2	TINYTEXT		''	will replace userData2 on parent order if exists
childData	TINYTEXT		''	will replace childData on parent order if exists
stkExecBrkrCode	VARCHAR(16)		''	will replace execBrkrCode on stk parent orders if exists
futExecBrkrCode	VARCHAR(16)		''	will replace execBrkrCode on fut parent orders if exists
eqtOptExecBrkrCode	VARCHAR(16)		''	will replace execBrkrCode on eqt optmleg parent orders if exists
futOptExecBrkrCode	VARCHAR(16)		''	will replace execBrkrCode on fut optmleg parent orders if exists
modifiedBy	VARCHAR(24)		''	user who last modified this record
modifiedIn	enum - SysEnvironment		'None'
timestamp	DATETIME(6)		'1900-01-01 00:00:00.000000'	timestamp of last modification
AltClearingList	JSON		'JSON_OBJECT()'

PRIMARY KEY DEFINITION (Unique)

Field	Sequence
ticker_tk	1
ticker_at	2
ticker_ts	3
srcAccnt	4
clientFirm	5

JSON Block (AltClearingList)

Field	Type	Comment
execBrkrCode	string
exDest	string	exDest eg CBOEOPT AMEXCOB NMSOPT NMSSTK NMSCOB CMXFUT
clrFlipType	enum - clrFlipType	type of clearing corp delivery
clrFlipFirm	string	deliverTo clearing member eg OCC NSCC MPID or InstitutionID
clrFlipAccnt	string	deliverTo client account eg OCC AID or a DVP FBO code
clrAgent	string	deliverTo agent eg DVP Agent Bank ID
clrTaxID	string	deliverTo taxID eg DVP TaxID
execBrkrAccnt	string	exec broker account supplied by exec broker usually Account1 only used if clrFlipTypeBrkrAccnt
execBrkrClFirm	string	exec broker clientfirm supplied by exec broker usually OnBehalfOfCompId115 only used if clrFlipTypeBrkrAccnt
execBrkrUserName	string	exec broker user name supplied by exec broker
badge	string	exchange memembership and badge id

CREATE TABLE EXAMPLE QUERY

CREATE TABLE `SRControl`.`MsgAltSymbolMap` (
    `ticker_at` ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') NOT NULL DEFAULT 'None',
    `ticker_ts` ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') NOT NULL DEFAULT 'None',
    `ticker_tk` VARCHAR(12) NOT NULL DEFAULT '',
    `srcAccnt` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'client enter order in this account',
    `clientFirm` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'SR assigned client firm acronym',
    `accnt` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'this accnt will replace accnt on parent order (if exists)',
    `altAccnt` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'will replace altAccnt on parent order (if exists)',
    `altUserName` VARCHAR(24) NOT NULL DEFAULT '' COMMENT 'will replace altUserName on parent order (if exists)',
    `strategy` VARCHAR(36) NOT NULL DEFAULT '' COMMENT 'will replace strategy on parent order (if exists)',
    `userData1` TINYTEXT NOT NULL DEFAULT '' COMMENT 'will replace userData1 on parent order (if exists)',
    `userData2` TINYTEXT NOT NULL DEFAULT '' COMMENT 'will replace userData2 on parent order (if exists)',
    `childData` TINYTEXT NOT NULL DEFAULT '' COMMENT 'will replace childData on parent order (if exists)',
    `stkExecBrkrCode` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'will replace execBrkrCode on stk parent orders (if exists)',
    `futExecBrkrCode` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'will replace execBrkrCode on fut parent orders (if exists)',
    `eqtOptExecBrkrCode` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'will replace execBrkrCode on eqt opt/mleg parent orders (if exists)',
    `futOptExecBrkrCode` VARCHAR(16) NOT NULL DEFAULT '' COMMENT 'will replace execBrkrCode on fut opt/mleg parent orders (if exists)',
    `modifiedBy` VARCHAR(24) NOT NULL DEFAULT '' COMMENT 'user who last modified this record',
    `modifiedIn` ENUM('None','Neptune','Pluto','V7_Stable','V7_Latest','Saturn','Venus','Mars','SysTest','V7_Current') NOT NULL DEFAULT 'None',
    `timestamp` DATETIME(6) NOT NULL DEFAULT '1900-01-01 00:00:00.000000' COMMENT 'timestamp of last modification',
    `AltClearingList` JSON NOT NULL DEFAULT JSON_OBJECT() CHECK(JSON_VALID(AltClearingList)),
    PRIMARY KEY USING HASH (`ticker_tk`,`ticker_at`,`ticker_ts`,`srcAccnt`,`clientFirm`)
) ENGINE=SRSE DEFAULT CHARSET=LATIN1 COMMENT='';

SELECT TABLE EXAMPLE QUERY

SELECT
    `ticker_at`,
    `ticker_ts`,
    `ticker_tk`,
    `srcAccnt`,
    `clientFirm`,
    `accnt`,
    `altAccnt`,
    `altUserName`,
    `strategy`,
    `userData1`,
    `userData2`,
    `childData`,
    `stkExecBrkrCode`,
    `futExecBrkrCode`,
    `eqtOptExecBrkrCode`,
    `futOptExecBrkrCode`,
    `timestamp`,
    `AltClearingList`
FROM `SRControl`.`MsgAltSymbolMap`
WHERE 
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */ 
    `ticker_at` = 'None'
  AND
    /* Replace with a ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') */ 
    `ticker_ts` = 'None'
  AND
    /* Replace with a VARCHAR(12) */ 
    `ticker_tk` = 'Example_ticker_tk'
  AND
    /* Replace with a VARCHAR(16) */ 
    `srcAccnt` = 'Example_srcAccnt'
  AND
    /* Replace with a VARCHAR(16) */ 
    `clientFirm` = 'Example_clientFirm';

UPDATE TABLE EXAMPLE QUERY

UPDATE `SRControl`.`MsgAltSymbolMap` 
SET
    /* Replace with a VARCHAR(16) */ 
    `accnt` = 'Example_accnt',
    /* Replace with a VARCHAR(16) */ 
    `altAccnt` = 'Example_altAccnt',
    /* Replace with a VARCHAR(24) */ 
    `altUserName` = 'Example_altUserName',
    /* Replace with a VARCHAR(36) */ 
    `strategy` = 'Example_strategy',
    /* Replace with a TINYTEXT */ 
    `userData1` = 'dummy tiny text',
    /* Replace with a TINYTEXT */ 
    `userData2` = 'dummy tiny text',
    /* Replace with a TINYTEXT */ 
    `childData` = 'dummy tiny text',
    /* Replace with a VARCHAR(16) */ 
    `stkExecBrkrCode` = 'Example_stkExecBrkrCode',
    /* Replace with a VARCHAR(16) */ 
    `futExecBrkrCode` = 'Example_futExecBrkrCode',
    /* Replace with a VARCHAR(16) */ 
    `eqtOptExecBrkrCode` = 'Example_eqtOptExecBrkrCode',
    /* Replace with a VARCHAR(16) */ 
    `futOptExecBrkrCode` = 'Example_futOptExecBrkrCode',
    /* Replace with a DATETIME(6) */
    `timestamp` = '2022-01-01 12:34:56.000000',
    /* Replace with a JSON */
    `AltClearingList` = '{"key": "value"}'
WHERE
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */ 
    `ticker_at` = 'None'
  AND
    /* Replace with a ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') */ 
    `ticker_ts` = 'None'
  AND
    /* Replace with a VARCHAR(12) */ 
    `ticker_tk` = 'Example_ticker_tk'
  AND
    /* Replace with a VARCHAR(16) */ 
    `srcAccnt` = 'Example_srcAccnt'
  AND
    /* Replace with a VARCHAR(16) */ 
    `clientFirm` = 'Example_clientFirm';

INSERT TABLE EXAMPLE QUERY

INSERT INTO `SRControl`.`MsgAltSymbolMap`(
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */ 
    `ticker_at`,
    /* Replace with a ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') */ 
    `ticker_ts`,
    /* Replace with a VARCHAR(12) */ 
    `ticker_tk`,
    /* Replace with a VARCHAR(16) */ 
    `srcAccnt`,
    /* Replace with a VARCHAR(16) */ 
    `clientFirm`,
    /* Replace with a VARCHAR(16) */ 
    `accnt`,
    /* Replace with a VARCHAR(16) */ 
    `altAccnt`,
    /* Replace with a VARCHAR(24) */ 
    `altUserName`,
    /* Replace with a VARCHAR(36) */ 
    `strategy`,
    /* Replace with a TINYTEXT */ 
    `userData1`,
    /* Replace with a TINYTEXT */ 
    `userData2`,
    /* Replace with a TINYTEXT */ 
    `childData`,
    /* Replace with a VARCHAR(16) */ 
    `stkExecBrkrCode`,
    /* Replace with a VARCHAR(16) */ 
    `futExecBrkrCode`,
    /* Replace with a VARCHAR(16) */ 
    `eqtOptExecBrkrCode`,
    /* Replace with a VARCHAR(16) */ 
    `futOptExecBrkrCode`,
    /* Replace with a DATETIME(6) */
    `timestamp`,
    /* Replace with a JSON */
    `AltClearingList`
) 
VALUES(
    'None',
    'None',
    'Example_ticker_tk',
    'Example_srcAccnt',
    'Example_clientFirm',
    'Example_accnt',
    'Example_altAccnt',
    'Example_altUserName',
    'Example_strategy',
    'dummy tiny text',
    'dummy tiny text',
    'dummy tiny text',
    'Example_stkExecBrkrCode',
    'Example_futExecBrkrCode',
    'Example_eqtOptExecBrkrCode',
    'Example_futOptExecBrkrCode',
    '2022-01-01 12:34:56.000000',
    '{"key": "value"}'
);

DELETE TABLE EXAMPLE QUERY

DELETE FROM `SRControl`.`MsgAltSymbolMap` 
WHERE
    /* Replace with a ENUM('None','EQT','IDX','BND','CUR','COM','FUT','SYN','WAR','FLX','MUT','SPD','MM','MF','COIN','TOKEN','ANY') */ 
    `ticker_at` = 'None'
  AND
    /* Replace with a ENUM('None','SR','NMS','CME','ICE','CFE','CBOT','NYMEX','COMEX','RUT','CIDX','ARCA','NYSE','OTC','NSDQ','MFQS','MIAX','DJI','CUSIP','ISIN','BXE','EUX','ANY','CXE','DXE','NXAM','NXBR','NXLS','NXML','NXOS','NXP','EUREX','CEDX','ICEFE') */ 
    `ticker_ts` = 'None'
  AND
    /* Replace with a VARCHAR(12) */ 
    `ticker_tk` = 'Example_ticker_tk'
  AND
    /* Replace with a VARCHAR(16) */ 
    `srcAccnt` = 'Example_srcAccnt'
  AND
    /* Replace with a VARCHAR(16) */ 
    `clientFirm` = 'Example_clientFirm';

Doc Columns Query

SELECT * FROM SRControl.doccolumns WHERE TABLE_NAME='AltSymbolMap' ORDER BY ordinal_position ASC;